"""
Case Type   : 数据库系统
Case Name   : 预编译sql为删除数据，执行预编译sql语句过程中修改表结构-修改字段长度
Create At   : 2021/10/12
Owner       : opentestcase024
Description :
    1.创建配置文件
    2.编译java文件
    3.运行java预编译代码（删除预编译重复执行）
    4.步骤3过程中，进行表结构变更-修改字段长度与预编译sql相关
    5.步骤3过程中，进行表结构变更-修改字段长度与预编译sql无关
    6.验证步骤3结果
Expect      :
    1.创建配置文件成功
    2.编译java文件成功
    3.运行java预编译代码（删除预编译重复执行），开始执行
    4.步骤3过程中，进行表结构变更-修改字段长度与预编译sql相关，修改成功
    5.步骤3过程中，进行表结构变更-修改字段长度与预编译sql无关，修改成功
    6.验证步骤3结果，正常执行完成
History     :
    Modified by opentestcase004 2022/9/23:优化用例，添加查询表数据量及参数查询步骤，
    便于问题定位
    Modified by opentestcase004 2022/11/22:与开发沟通，清理环境添加步骤，便于后续定位
"""
import os
import time
import unittest

from testcase.utils.ComThread import ComThread
from testcase.utils.Common import Common
from testcase.utils.CommonSH import CommonSH
from testcase.utils.Constant import Constant
from testcase.utils.Logger import Logger
from yat.test import Node
from yat.test import macro


class JDBCPrepareStatement(unittest.TestCase):

    def setUp(self):
        self.log = Logger()
        self.pri_dbuser = Node(node='PrimaryDbUser')
        self.pri_root = Node(node='PrimaryRoot')
        self.log.info("----this is setup----")
        self.log.info(f"-----{os.path.basename(__file__)} start-----")
        self.target_path = os.path.join(macro.DB_BACKUP_PATH, "JDBC_test")
        self.properties = os.path.join(self.target_path,
                                       "jdbc_preparestat_Case0008.properties")
        self.java_name = "jdbc_PrepareStatement_Case0006_10"
        self.tb_name = "tb_jdbc_PrepareStatement_Case0008"
        self.com = Common()
        self.constant = Constant()
        self.pri_sh = CommonSH('PrimaryDbUser')
        self.result = self.com.show_param('synchronous_standby_names')
        self.result1 = self.com.show_param('synchronous_commit')
        self.log_statement = self.com.show_param('log_statement')
        if self.log_statement != 'all':
            self.re = self.pri_sh.execute_gsguc(
                'reload', self.constant.GSGUC_SUCCESS_MSG,
                'log_statement = all')
            self.log.info(self.re)
            self.assertTrue(self.re, '执行失败')
        self.log_min_duration_statement = self.com.show_param(
            'log_min_duration_statement ')
        if self.log_statement != '0':
            self.re1 = self.pri_sh.execute_gsguc(
                'reload', self.constant.GSGUC_SUCCESS_MSG,
                'log_min_duration_statement = 0')
            self.log.info(self.re1)
            self.assertTrue(self.re1, '执行失败')

    def test_main(self):
        step_txt = '----step1:创建配置文件; expect:创建成功----'
        self.log.info(step_txt)
        self.com.scp_file(self.pri_root,
                          f"{self.java_name}.java", self.target_path)
        result = self.pri_root.sh(f"touch {self.properties}").result()
        self.log.info(result)
        config = f'echo "password=' \
            f'{self.pri_dbuser.db_password}"> {self.properties}'
        self.pri_root.sh(config)
        config = f'echo "port={self.pri_dbuser.db_port}">> ' \
            f'{self.properties}'
        self.pri_root.sh(config)
        config = f'echo "hostname={self.pri_dbuser.db_host}">> ' \
            f'{self.properties}'
        self.pri_root.sh(config)
        config = f'echo "user={self.pri_dbuser.db_user}">> ' \
            f'{self.properties}'
        self.pri_root.sh(config)
        config = f'echo "dbname={self.pri_dbuser.db_name}">> ' \
            f'{self.properties}'
        self.pri_root.sh(config)
        config = f'echo "tbname={self.tb_name}">> {self.properties}'
        self.pri_root.sh(config)
        config = f'cat {self.properties}'
        result = self.pri_root.sh(config).result()
        self.log.info(result)
        self.assertTrue("password=" in result and "port=" in result
                        and "hostname=" in result and "user=" in result
                        and "dbname=" in result and "tbname=" in result,
                        "执行失败" + step_txt)

        step_txt = '----step2:编译java文件; expect:编译成功----'
        self.log.info(step_txt)
        self.pri_root.scp_put(macro.JDBC_PATH,
                              f"{self.target_path}/postgresql.jar")
        cmd = f"javac -encoding utf-8 -cp " \
            f"{os.path.join(self.target_path, 'postgresql.jar')} " \
            f"{os.path.join(self.target_path, f'{self.java_name}.java')}"
        self.log.info(cmd)
        result = self.pri_root.sh(cmd).result()
        self.log.info(result)
        self.assertEqual('', result, "执行失败" + step_txt)

        step_txt = '----step3:运行java预编译代码（删除预编译重复执行）; ' \
                   'expect:开始运行----'
        self.log.info(step_txt)
        java_cmd = f"java -cp " \
            f"{os.path.join(self.target_path, 'postgresql.jar')}" \
            f":{self.target_path} " \
            f"{self.java_name} -F {self.properties}"
        self.log.info(java_cmd)
        java_thread = ComThread(self.com.get_sh_result,
                                args=(self.pri_root, java_cmd))
        java_thread.setDaemon(True)
        java_thread.start()
        time.sleep(10)

        step_txt = '----step4:步骤3过程中，进行表结构变更-修改字段长度与预编译' \
                   'sql相关; expect:修改成功----'
        self.log.info(step_txt)
        alter_sql = f'alter table {self.tb_name} ' \
            f'alter COLUMN c_customer_sk type char(40);\d {self.tb_name}'
        result = self.pri_sh.execut_db_sql(alter_sql)
        self.log.info(result)
        self.assertIn(self.constant.ALTER_TABLE_MSG, result,
                      "执行失败" + step_txt)

        step_txt = '----step5:步骤3过程中，进行表结构变更-修改字段长度与预编译' \
                   'sql无关； expect:修改成功----'
        self.log.info(step_txt)
        alter_sql = f'alter table {self.tb_name} ' \
            f'alter column c_customer_name type varchar(22);\d {self.tb_name}'
        result = self.pri_sh.execut_db_sql(alter_sql)
        self.log.info(result)
        self.assertIn(self.constant.ALTER_TABLE_MSG, result,
                      "执行失败" + step_txt)

        step_txt = '----step6:验证步骤3结果; expect:正常执行完成----'
        self.log.info(step_txt)
        java_thread.join(180)
        java_result = java_thread.get_result()
        select_sql = f"select count(*) from {self.tb_name};"
        self.log.info(select_sql)
        result = self.pri_sh.execut_db_sql(select_sql)
        self.log.info(result)
        suc_flag = "delete succeed!999"
        self.assertIn(suc_flag, java_result, "执行失败" + step_txt)

    def tearDown(self):
        self.log.info('----this is tearDown----')
        self.log.info('----清理环境----')
        cmd = f"select * from dbe_perf.statement where query like " \
            f"'%{self.tb_name}%';"
        self.log.info(cmd)
        sql_result = self.pri_sh.execut_db_sql(cmd)
        self.log.info(sql_result)
        ps_cmd = f"ps ux |grep {self.java_name}|grep -v grep|" \
            f"tr -s ' '|cut -d ' ' -f 2"
        self.log.info(ps_cmd)
        ps_result = self.pri_root.sh(ps_cmd).result()
        self.log.info(ps_result)
        if ps_result != '':
            self.log.info('----清理java进程----')
            kill_cmd = f"ps ux |grep {self.java_name}|grep -v grep|" \
                f"tr -s ' '|cut -d ' ' -f 2|xargs kill -9"
            self.log.info(kill_cmd)
            self.pri_root.sh(kill_cmd)

        cmd = f"drop table if exists {self.tb_name};"
        result = self.pri_sh.execut_db_sql(cmd)
        self.log.info(result)
        cmd = f"rm -rf {self.target_path}"
        self.log.info(cmd)
        self.pri_root.sh(cmd)
        self.re = self.pri_sh.execute_gsguc(
            'reload', self.constant.GSGUC_SUCCESS_MSG,
            f'log_statement = {self.log_statement}')
        self.log.info(self.re)
        self.assertTrue(self.re, '执行失败')
        self.re1 = self.pri_sh.execute_gsguc(
            'reload', self.constant.GSGUC_SUCCESS_MSG,
            f'log_min_duration_statement = {self.log_min_duration_statement}')
        self.log.info(self.re1)
        self.assertTrue(self.re1, '执行失败')
        self.log.info(f"-----{os.path.basename(__file__)} end-----")
